﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using MySql.Data.MySqlClient;

namespace Ddbs.QE.DBMSLibrary
{
    /// <summary>
    /// Connect to MySQL database
    /// MySql will use connection pool by default.
    /// </summary>
    public class MySQLHandler
    {
        private static string connectString = "server=127.0.0.1;uid=root;" + "pwd=mysql2011;database=";
        public static DataSet QueryRequest(string dbname, string queryCommand)
        {
            DataSet myData = new DataSet();
            MySql.Data.MySqlClient.MySqlConnection conn;
            MySql.Data.MySqlClient.MySqlCommand cmd;
            MySql.Data.MySqlClient.MySqlDataAdapter myAdapter;

            conn = new MySql.Data.MySqlClient.MySqlConnection();
            cmd = new MySql.Data.MySqlClient.MySqlCommand();
            myAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter();

            //myAdapter.TableMappings
            conn.ConnectionString = connectString + dbname +";";
            // "server=127.0.0.1;uid=root;" +     "pwd=12345;database=test;";

            try
            {
                cmd.CommandText = queryCommand;
  
                cmd.Connection = conn;
                myAdapter.SelectCommand = cmd;
                myAdapter.Fill(myData);                
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                // display information
                throw new Exception(ex.Message);
            }
           
            return myData;
        }


        /// <summary>
        /// Get connection string
        /// </summary>
        /// <param name="server"></param>
        /// <param name="uid"></param>
        /// <param name="pwd"></param>
        /// <param name="database"></param>
        /// <returns></returns>
        public static string GetConnectionString(string server, string uid, string pwd, string database)
        {
            string connString = null;
            string format = "Server={0};Uid={1};Pwd={2};Database={3};port=3306;";
            if (!string.IsNullOrEmpty(server)
                && !string.IsNullOrEmpty(uid)
                && !string.IsNullOrEmpty(pwd)
                && !string.IsNullOrEmpty(database))
            {
                connString = string.Format(format, server, uid, pwd, database);
            }
            return connString;
        }

        /// <summary>
        /// Get connection string by some default parameters
        /// </summary>
        /// <param name="database"></param>
        /// <returns></returns>
        public static string GetConnectionString(string database)
        {
            string connString = null;
            string format = "Server=127.0.0.1;Uid=root;Pwd=mysql2011;Database={0};port=3306;";
            if (!string.IsNullOrEmpty(database))
            {
                connString = string.Format(format, database);
            }
            return connString;
        }

        /// <summary>
        /// Get Connection string by site id
        /// </summary>
        /// <param name="siteID"></param>
        /// <returns></returns>
        public static string GetConnectionString(int siteID)
        {
            string connString = null;
            string format = "Server=127.0.0.1;Uid=root;Pwd=mysql2011;Database=ddb_{0};port=3306;";
            
                connString = string.Format(format, siteID);
            
            return connString;
        }

        public static void Main()
        {
            DataSet r = QueryRequest("ddb1", "select * from book;");
            Console.WriteLine(r.Tables.Count);
            var i = r.Tables.GetEnumerator()   ;
            
             while (i.MoveNext())
            {
                 var table = (DataTable)(i.Current);
                 var j = table.Columns.GetEnumerator();
                 
                 while (j.MoveNext())
                 {
                     var col = (DataColumn)j.Current;
                     Console.WriteLine(col.ColumnName);
                     
                 }
                 Console.WriteLine(table.TableName);

            }
            
        }
    }
}
